﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Threading;

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the 
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

namespace agtest
{
	/// <summary>
	/// Sample code demonstrating how to test AlwaysOn Availabilty group failover performance.
	/// 
	/// Using this code is not recommended for use against any production AlwaysOn database
	/// since it triggers forced failovers which may result in data loss.
	/// 
	/// Always perform testing against a non-production AlwaysOn configuration for example 
	/// an AlwaysOn system in pre-deployment.  Only test against empty test
	/// databases or databases with test-only data, as any failover can incur data loss.
	/// </summary>
	class Program
	{
		// Notes about the failover technique used here:
		//
		// To capture a true "end-to-end" measure of a failover time, the following technique is employed:
		//
		// 1. First a connection to the primary database is established and information about the 
		//    availability group is determined (ag name, replicas, etc...).  If this fails, the test
		//    cannot proceed.
		// 2. Once all meta-data is gathered, we have a list of replicas for the database and the current
		//    primary replica is known.
		// 3. Next we start failover looping.
		// 4. Failover looping first verifies that all replicas are synchronizing, it does not proceed
		//    until this is the case.
		// 5. Once all replicas are synchronizing, the main thread verifies the current primary and loops
		//    connecting to the primary every 1 second.   At each connection success it starts a timer.
		// 6. After a short period a secondary thread is launched to force a failover.  The next primary
		//    is picked using simple round robin (replica names in alphabetical order).  The secondary
		//    thread forces a failover and resumes data movement on all replicas.
		// 7. Meanwhile the main thread is failing to connect to the primary and re-attempting to connect
		//    every 1 second.  Once the main thread successfully connects to a new primary, it stops the
		//    failover timer.  Hence we measure time from last know successful connection to old primary
		//    to the first good connection to new primary, true "end-to-end" failover performance from
		//    the client driver's perspective.
		// 8. Note the failover attempt may fail and the primary may not fail over or recover.  The program
		//    attempts to be resilient to these issues and fallback and retry synchronization and start a new failover
		//    attempt.

		/// <summary>
		/// Default command timeout for all T-SQL commands run (see xsql, xsql_ne, and xscalar).
		/// </summary>
		private static readonly TimeSpan TSQLCommandTimeout = TimeSpan.FromSeconds(120);

		/// <summary>
		/// T-SQL retry time for all T-SQL commands.
		/// </summary>
		private static readonly TimeSpan TSQLRetryInterval = TimeSpan.FromSeconds(5);

		// Due to the asynchronous nature of AlwaysOn failovers, I use a small number of wait intervals here to improve
		// stability of the failover algorithm.  For example, when you trigger a forced failover, this command takes
		// time to complete, and you need to wait a small period before attempting to resume data movement, otherwise
		// the resume data movement will occur prior to the failover completion (for example).

		// In addition, there are wait periods here to allow the system time to recover if it appears to be slow to recover.

		/// <summary>
		/// Time to wait before re-attempting to wait for synchronization.
		/// This is used to give the system some additional time to recover when synchronization startup is not responsive.
		/// </summary>
		private static readonly TimeSpan WaitForSynchronizingRetryInterval = TimeSpan.FromSeconds(30);

		/// <summary>
		/// Time to wait before triggering failover.
		/// The loop algorithm will first wait for all replicas to be fully synchronized (WaitForSynchronizing),
		/// then wait FailoverWarmupInterval, then attempt to trigger a failover.
		/// </summary>
		private static readonly TimeSpan FailoverWarmupInterval = TimeSpan.FromSeconds(10);

		/// <summary>
		/// Extra period of time to wait when fail count is exceeded, allows system time to recover.
		/// </summary>
		private static readonly TimeSpan FailCountExceededInterval = TimeSpan.FromSeconds(30);

		/// <summary>
		/// Time to wait before retrying failover attempt.
		/// If the attempt to failover fails, the primary thread will keep looping and waiting for the primary to
		/// change.  Once FailoverReAttemptInterval is exceeded, the primary thread will drop back to WaitForSynchronizing
		/// to try to re-establish synchronization and then trigger another failover loop.
		/// </summary>
		private static readonly TimeSpan FallBackToWaitForSynchronizingInterval = TimeSpan.FromMinutes(10);

		/// <summary>
		/// Time to wait after forcing failover before starting attempts to resume data movement.
		/// </summary>
		private static readonly TimeSpan ForceFailoverWaitTime = TimeSpan.FromSeconds(5);

		/// <summary>
		/// Time to wait after resuming data movement before starting attempts to verify synchronization.
		/// </summary>
		private static readonly TimeSpan ResumeWaitTime = TimeSpan.FromSeconds(2);

		/// <summary>
		/// Maximum attempts to wait for sync before failing.
		/// </summary>
		const int MaxWaitForSyncRetryCount = 10;

		/// <summary>
		/// Maximum number of failures to log per failover attempt.
		/// </summary>
		const int MaxLoggedFailuresPerFailover = 5;

		/// <summary>
		/// Maximum number of connection failures to force a restart of the failover algorithm.
		/// </summary>
		const int MaxFailureCountToForceRetry = 100;

		/// <summary>
		/// Log file writer and file name.
		/// </summary>
		private static StreamWriter logWriter = null;
		private static string logFileName;

		/// <summary>
		/// Tag to track currently running test for logging purposes.
		/// </summary>
		static string currentRunningTest = "";

		static void Main(string[] args)
		{
			// Process command line parameters.
			FailoverTestParameters ftp = FailoverTestParameters.ParseCommandLine(args);
			if (null == ftp)
			{
				return;
			}

			// Set currentRunningTest used by logger.
			currentRunningTest = ftp.Tag;

			// Setup log file for output.
			string timeStampExtension = DateTime.UtcNow.ToString("s").Replace("-", "").Replace(":", "").Replace("T", "_");
			logFileName = ftp.Tag + "_AgTest_" + timeStampExtension + ".log";
			logFileName = MakeSafeForFileName(logFileName);
			logWriter = new StreamWriter(logFileName);

			// Dump ADO.NET and SNAC versions.
			LogDriverVersions(ftp);

			// Get configuration for primary and secondary.
			if (!LoadPrimaryAndSecondaryConfiguration(ftp)) return;

			// Setup failover status database and table.
			if (!string.IsNullOrEmpty(ftp.LogServer))
			{
				SetupFailoverStatsDatabase(ftp);
			}

			// Run test.
			RunOneFailoverTest(ftp);
		}

		/// <summary>
		/// Removes any unsafe file name characters from string.
		/// </summary>
		private static string MakeSafeForFileName(string s)
		{
			if (string.IsNullOrEmpty(s)) return "";
			char[] charsToReplace = Path.GetInvalidFileNameChars();
			foreach (char c in charsToReplace)
			{
				s = s.Replace(c.ToString(), "");
			}
			return s;
		}

		/// <summary>
		/// Logs SQL Server driver versions (ADO.NET, SNAC, and SQLServer).
		/// </summary>
		private static void LogDriverVersions(FailoverTestParameters ftp)
		{
			FileVersionInfo fvi = FileVersionInfo.GetVersionInfo(typeof(SqlConnection).Assembly.Location);
			LogStatus("ADO.NET file version {0}", fvi.ProductVersion);

			try
			{
				string snacFileName = Path.Combine(Environment.SystemDirectory, "sqlncli11.dll");
				fvi = FileVersionInfo.GetVersionInfo(snacFileName);
				LogStatus("SNAC 11 file version {0}", fvi.ProductVersion);
			}
			catch (Exception ex)
			{
				LogStatus(ex.Message);
				// Ignore exception.
			}

			try
			{
				string sqlFileName = Path.Combine(Environment.SystemDirectory, "sqlsrv32.dll");
				fvi = FileVersionInfo.GetVersionInfo(sqlFileName);
				LogStatus("SqlServer file version {0}", fvi.ProductVersion);
			}
			catch (Exception ex)
			{
				LogStatus(ex.Message);
			}
		}

		/// <summary>
		/// Sets up FailoverStatistics database which tracks test stats.
		/// If creation of database and stats table fail, disables logging to stats table.
		/// </summary>
		private static void SetupFailoverStatsDatabase(FailoverTestParameters ftp)
		{
			string logServer = ftp.LogServer;
			string fields = "EventTime datetime2,Tag varchar(255),SourceServer varchar(255),TargetServer varchar(255)," +
					"Driver varchar(255),UseMultiSubnetFailover bit,UseReadOnlyIntent bit,UseDbMirroring bit,Iteration int," +
					"FailoverMilliseconds int,ErrorCount int,ExceptionData varchar(max)";
			FieldListBuilder flb = new FieldListBuilder(fields);

			xsql_ne(logServer, "master", "create database FailoverStatistics");
			xsql_ne(logServer, "FailoverStatistics",
				"create table FailoverStatistics (" + flb.FieldListWithType + ")");
			xsql_ne(logServer, "FailoverStatistics",
				"create procedure UploadFailoverStats(" + flb.ParameterListWithType + ") as insert into FailoverStatistics (" + flb.FieldList + ") values (" + flb.ParameterList + ")");
			try
			{
				// Verify that FailoverStatistics table exists, if so, enable logging of stats.
				xsql(logServer, "FailoverStatistics", "select count(*) from FailoverStatistics");
				LogStatus("Logging statistics to table FailoverStatistics in database FailoverStatistics.");
				ftp.LogStatistics = true;
			}
			catch(Exception ex)
			{
				LogStatus("SetupFailoverStatsDatabase failed, disabling logging of failover statistics to FailoverStatistics database => {0}", ex.Message);
				ftp.LogStatistics = false;
			}
		}

		/// <summary>
		/// Runs a single failover test, uploads stats to failstats table.
		/// </summary>
		static void RunOneFailoverTest(FailoverTestParameters ftp)
		{
			LogStatus("AvailabilityGroupId    = {0}", ftp.AvailabilityGroupId);
			LogStatus("AvailabilityGroupName  = {0}", ftp.AvailabilityGroupName);
			LogStatus("ConnectionString       = {0}", ftp.ConnectionString);
			LogStatus("Database               = {0}", ftp.Database);
			LogStatus("DriverToUse            = {0}", ftp.DriverToUse);
			LogStatus("FailoverLoops          = {0}", ftp.FailoverLoops);
			LogStatus("FailoverPartner        = {0}", ftp.FailoverPartner);
			LogStatus("LogServer              = {0}", ftp.LogServer);
			LogStatus("Replicas               = {0}", ftp.ReplicasForDisplay);
			LogStatus("ReplicaCount           = {0}", ftp.ReplicaCount);
			LogStatus("Server                 = {0}", ftp.Server);
			LogStatus("Tag                    = {0}", ftp.Tag);
			LogStatus("UseMultiSubnetFailover = {0}", ftp.UseMultiSubnetFailover);
			LogStatus("UseReadOnlyIntent      = {0}", ftp.UseReadOnlyIntent);
			LogStatus("UseDatabaseMirroring   = {0}", ftp.UseDatabaseMirroring);

			List<FailoverData> failoverData = new List<FailoverData>();
			for (int i = 1; i <= ftp.FailoverLoops; i++)
			{
				if (!WaitForSynchronizing(ftp))
				{
					Sleep(WaitForSynchronizingRetryInterval);
					continue;
				}
				LogStatus("Failover {0} of {1}...", i, ftp.FailoverLoops);
				RunFailoverTest(ftp, failoverData, i);
				UploadFailoverStats(ftp, failoverData);
			}
		}

		/// <summary>
		/// Connects to primary and loads availability group name and availability group id into FailoverTestParameters.
		/// Also loads physical instance names for primary and secondary into FailoverTestParameters.
		/// </summary>
		static bool LoadPrimaryAndSecondaryConfiguration(FailoverTestParameters ftp)
		{
			try
			{
				using (SqlConnection conn = ftp.GetNewSqlConnection())
				{
					// Fetch availability group name and id given database name.
					conn.Open();
					SqlCommand cmd = conn.CreateCommand();
					cmd.CommandTimeout = TSQLCommandTimeout.Seconds;
					cmd.CommandText =
						"select ag.name, ag.group_id from sys.availability_databases_cluster adc " +
							"inner join sys.availability_groups ag on adc.group_id=ag.group_id where adc.database_name = @dbname";
					cmd.Parameters.Add("@dbname", SqlDbType.NVarChar, 128);
					cmd.Parameters["@dbname"].Value = ftp.Database;
					using (SqlDataReader dr = cmd.ExecuteReader())
					{
						dr.Read();
						ftp.AvailabilityGroupName = dr.GetString(0).ToLower();
						ftp.AvailabilityGroupId = dr.GetGuid(1);
					}

					// Check count of replicas, should be 2 or greater.
					cmd = conn.CreateCommand();
					cmd.CommandText = "select cast(count(*) as int) from sys.availability_replicas where group_id = @groupid";
					cmd.Parameters.Add("@groupid", SqlDbType.UniqueIdentifier);
					cmd.Parameters["@groupid"].Value =  ftp.AvailabilityGroupId;
					ftp.ReplicaCount = (int)cmd.ExecuteScalar();

					if (ftp.ReplicaCount < 2)
					{
						LogStatus("Expecting 2 or more replicas, cannot continue.");
						return false;
					}

					// Get list of replica's given availability group id.
					// These are in alphabetical order just for sake of picking one database to upload stats (NodeServer[0]).
					cmd.CommandText = "select replica_server_name from sys.availability_replicas where group_id = @groupid order by replica_server_name";
					using (IDataReader dr = cmd.ExecuteReader())
					{
						while (dr.Read())
						{
							ftp.Replicas.Add(dr.GetString(0).ToLower());
						}
					}

					// If failover partner is set and replica count is not 2, fail now.
					if (ftp.UseDatabaseMirroring && ftp.ReplicaCount != 2)
					{
						LogStatus("Database mirroring algorithm only works if there are 2 replicas in the availability group, found {0} replicas, test exiting.", ftp.ReplicaCount);
						return false;
					}
				}
				return true;
			}
			catch (Exception ex)
			{
				Console.WriteLine("ConfigurePrimaryAndSecondary failure => {0}", ex.Message);
				return false;
			}
		}

		/// <summary>
		/// Runs a single failover test.
		/// </summary>
		/// <param name="ftp">Failover test parameters driving test</param>
		/// <param name="failoverData">List to accumulate failover data</param>
		static void RunFailoverTest(FailoverTestParameters ftp, List<FailoverData> failoverData, int iteration)
		{
			Stopwatch connectionAttemptTime = new Stopwatch();
			Stopwatch failoverTestTime = new Stopwatch();
			int loops = 0;
			int failCount = 0;
			string currentServer = "";
			bool failoverPending = false;
			StringBuilder exceptionData = new StringBuilder(10000);

			for (;;)
			{
				try
				{
					using (IDbConnection conn = ftp.GetNewConnection())
					{
						conn.Open();
						IDbCommand cmd = conn.CreateCommand();
						cmd.CommandText = "select @@servername";
						using (IDataReader dr = cmd.ExecuteReader())
						{
								dr.Read();
								if (0 == loops)
								{
									ftp.PrimaryServer = dr.GetString(0).ToLower();
									LogStatus("Initial primary server is {0}. Looping until failover...", ftp.PrimaryServer);
									failoverTestTime.Start();
								}
								else
								{
									currentServer = dr.GetString(0).ToLower();
									if (!currentServer.Equals(ftp.PrimaryServer))
									{
										failoverPending = false;
										connectionAttemptTime.Stop();
										LogStatus("Failover from {0} to {1} complete in {2} milliseconds.", 
											ftp.PrimaryServer, currentServer, connectionAttemptTime.ElapsedMilliseconds);
										failoverData.Add(new FailoverData(ftp.PrimaryServer, currentServer, connectionAttemptTime.Elapsed, failCount, iteration, exceptionData.ToString()));
										return;
									}
								}
						}
					}
					loops++;
					Thread.Sleep(ftp.ConnectionRetryMs);

					// Kick off the failover once warmup interval is exceeded.
					if (!failoverPending && failoverTestTime.Elapsed > FailoverWarmupInterval)
					{
						Thread t = new Thread(new ParameterizedThreadStart(FailoverSQL));
						t.Start(ftp);

						// Track failover pending state.
						failoverPending = true;
					}

					// If failover is pending and we exceed FallBackToWaitForSynchronizingInterval, fail here.
					if (failoverPending && failoverTestTime.Elapsed > FallBackToWaitForSynchronizingInterval)
					{
						LogStatus("FallBackToWaitForSynchronizingInterval exceeded during failover attempt, restarting test.");
						return;
					}

					// Restart connection attempt timer.
					connectionAttemptTime.Restart();
				}
				catch (Exception ex)
				{
					failCount++;

					exceptionData.Append(string.Format("FailCount=>{0} Exception=> Message={1}", failCount, ex.Message));

					if (ex is SqlException)
					{
						SqlException sqlEx = (SqlException)ex;
						exceptionData.Append(string.Format("SqlException=> Server={0} LineNumber={1} Error={2} State={3} ",
							sqlEx.Server, sqlEx.LineNumber, sqlEx.Number, sqlEx.State));
					}
					else
					if (ex is OdbcException)
					{
						OdbcException odbcEx = (OdbcException)ex;
						exceptionData.Append(string.Format("OdbcException=> Message={0} ErrorCode={1}", odbcEx.Message, odbcEx.ErrorCode));
						foreach (OdbcError err in odbcEx.Errors)
						{
							exceptionData.Append(string.Format("OdbcError=> Message={0} NativeError={1} SQLState={2} ",
								err.Message, err.NativeError, err.SQLState));
						}
					}

					Sleep(ftp.ExceptionRetryMs);

					if (failCount <= MaxLoggedFailuresPerFailover)
					{
						if (ftp.LogFailoverErrors)
						{
							LogStatus("ExecFailoverTest[{0}] => {1}", ftp.PrimaryServer, CleanExceptionMessage(ex));
						}
					}
					else
					if (failCount > MaxFailureCountToForceRetry)
					{
						LogStatus("FailCount exceeding {0}, sleeping for {1} seconds then restarting test.",
							MaxFailureCountToForceRetry, FailCountExceededInterval.Seconds);
						Sleep(FailCountExceededInterval);
						return;
					}
				}
			}
		}

		/// <summary>
		/// Uploads all pending failover stats. Clears stats on success.
		/// </summary>
		static void UploadFailoverStats(FailoverTestParameters ftp, List<FailoverData> failoverData)
		{
			// If logging to database is disabled, just dump existing stats and return.
			if (!ftp.LogStatistics || string.IsNullOrEmpty(ftp.LogServer))
			{
				failoverData.Clear();
				return;
			}

			try
			{
				// Upload data.
				using (SqlConnection conn = GetConnection(ftp.LogServer, "FailoverStatistics"))
				{
					SqlTransaction trans = conn.BeginTransaction();
					SqlCommand cmdInsert = conn.CreateCommand();
					cmdInsert.CommandType = CommandType.StoredProcedure;
					cmdInsert.CommandText = "UploadFailoverStats";
					cmdInsert.Transaction = trans;
					SqlCommandBuilder.DeriveParameters(cmdInsert);
					for (int i = 0; i < failoverData.Count; i++)
					{
						cmdInsert.Parameters["@EventTime"].Value = failoverData[i].EventTime;
						cmdInsert.Parameters["@Tag"].Value = ftp.Tag;
						cmdInsert.Parameters["@SourceServer"].Value = failoverData[i].SourceServer;
						cmdInsert.Parameters["@TargetServer"].Value = failoverData[i].TargetServer;
						cmdInsert.Parameters["@Driver"].Value = ftp.DriverToUse.ToString();
						cmdInsert.Parameters["@UseMultiSubnetFailover"].Value = ftp.UseMultiSubnetFailover;
						cmdInsert.Parameters["@UseReadOnlyIntent"].Value = ftp.UseReadOnlyIntent;
						cmdInsert.Parameters["@UseDbMirroring"].Value = ftp.UseDatabaseMirroring;
						cmdInsert.Parameters["@Iteration"].Value = failoverData[i].Iteration;
						cmdInsert.Parameters["@FailoverMilliseconds"].Value = failoverData[i].FailoverInterval.TotalMilliseconds;
						cmdInsert.Parameters["@ErrorCount"].Value = failoverData[i].Errors;
						cmdInsert.Parameters["@ExceptionData"].Value = failoverData[i].ExceptionData;
						cmdInsert.ExecuteNonQuery();
					}
					trans.Commit();
				}

				// Clear failoverData on success, otherwise don't clear it as next attempt to upload may succeed.
				failoverData.Clear();
			}
			catch (Exception ex)
			{
				LogStatus("Failure uploading test data => {0}", ex.Message);
			}
		}

		/// <summary>
		/// Logs status to master log file.
		/// </summary>
		static void LogStatus(string format, params object[] args)
		{
			string message = string.Format(format, args);
			string status = string.Format("{0} {1} {2}", LogDateTime(), currentRunningTest, message);
			lock (logWriter)
			{
				Console.WriteLine(status);
				logWriter.WriteLine(status);
				logWriter.Flush();
			}
		}

		/// <summary>
		/// Returns current UTC time string in compact log format.
		/// </summary>
		static string LogDateTime()
		{
			DateTime logTime = DateTime.UtcNow;
			return string.Format("{0}-{1:D2}-{2:D2} {3:D2}:{4:D2}:{5:D2}.{6:D3}",
				logTime.Year, logTime.Month, logTime.Day,
				logTime.Hour, logTime.Minute, logTime.Second, logTime.Millisecond);
		}

		/// <summary>
		/// Fails over sql 
		/// </summary>
		/// <param name="nodeNameObject">Current primary node</param>
		static void FailoverSQL(object testParameters)
		{
			FailoverTestParameters ftp = (FailoverTestParameters)testParameters;

			// Pick next server in the list.
			for (int i = 0; i < ftp.Replicas.Count; i++)
			{
				if (ftp.Replicas[i].Equals(ftp.PrimaryServer))
				{
					ftp.FailoverServer = ftp.Replicas[(i+1) % ftp.Replicas.Count];
				}
			}

			LogStatus("Force failover from {0} => {1}", ftp.PrimaryServer, ftp.FailoverServer);
			string failoverSql = string.Format("alter availability group [{0}] force_failover_allow_data_loss", ftp.AvailabilityGroupName);
			xsql(ftp.FailoverServer, "master", failoverSql, 5, "FailoverSQL force failover");
			Sleep(ForceFailoverWaitTime);

			// Resume data movement on each node.
			foreach (string node in ftp.Replicas)
			{
				xsql(node, "master", string.Format("alter database [{0}] set hadr resume", ftp.Database), 5, "FailoverSQL resume");
			}
		}

		/// <summary>
		/// Wait until all nodes are up and synchronizing.
		/// </summary>
		/// <returns>true if nodes are synchronized, false if any failures occurred</returns>
		static bool WaitForSynchronizing(FailoverTestParameters ftp)
		{
			string sql = string.Format("select count(*) from sys.dm_hadr_availability_replica_states ars " + 
				"inner join sys.availability_replicas ar on ar.replica_id=ars.replica_id " + 
				"inner join sys.availability_groups ag on ag.group_id=ar.group_id " + 
				"where ag.name = '{0}' and (ars.synchronization_health=2 or ars.synchronization_health=1)", ftp.AvailabilityGroupName);

			LogStatus("WaitForSynchronizing starting...");

			int failCount = 0;
			int retryCount = 0;
			for (;;)
			{
				// Resume data movement on each node, then wait a few seconds.
				foreach (string node in ftp.Replicas)
				{
					xsql(node, "master", string.Format("alter database [{0}] set hadr resume", ftp.Database), 5, "WaitForSynchronizing resume");
				}
				Sleep(ResumeWaitTime);

				try
				{
					// Wait until all nodes are synchronizing.
					int maxReplicaCount = 0;
					foreach (string node in ftp.Replicas)
					{
						int replicaCount = xscalar(node, "master", sql);
						if (replicaCount > maxReplicaCount) 
						{
							maxReplicaCount = replicaCount;
						}
					}
					

					if (ftp.ReplicaCount == maxReplicaCount)
					{
						Sleep(ResumeWaitTime);
						LogStatus("WaitForSynchronizing success.");
						return true;
					}
				}
				catch (Exception ex)
				{
					failCount++;

					// Ignore first few errors for logging, not critical.
					if (failCount > 2)
					{
						LogStatus("WaitForSynchronizing => " + CleanExceptionMessage(ex));
					}
					if (failCount > 5)
					{
						LogStatus("WaitForSynchronizing failed.");
						return false;
					}
				}

				retryCount++;
				if (retryCount > MaxWaitForSyncRetryCount)
				{
					LogStatus("WaitForSynchronizing failed, exceeded retry count of 10");
					return false;
				}
			}
		}

		/// <summary>
		/// Opens connection to node given the node name.
		/// </summary>
		static SqlConnection GetConnection(string nodeName, string database)
		{
			SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
			csb.DataSource = nodeName;
			csb.IntegratedSecurity = true;
			csb.InitialCatalog = database;
			csb.Pooling = false;
			SqlConnection conn = new SqlConnection(csb.ConnectionString);
			conn.Open();
			return conn;
		}

		/// <summary>
		/// Sleeps for specified interval.
		/// </summary>
		private static void Sleep(TimeSpan interval)
		{
			Sleep((int)interval.TotalMilliseconds);
		}

		/// <summary>
		/// Sleeps for specified interval in milliseconds.
		/// </summary>
		private static void Sleep(int interval)
		{
			Thread.Sleep(interval);
		}

		/// <summary>
		/// Runs scalar command returning int against given node and database.
		/// </summary>
		static int xscalar(string nodeName, string database, string sql)
		{
			using (SqlConnection conn = GetConnection(nodeName, database))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					cmd.CommandTimeout = TSQLCommandTimeout.Seconds;
					return (int)cmd.ExecuteScalar();
				}
			}
		}

		/// <summary>
		/// Runs sql statement against specified node and database.
		/// </summary>
		static void xsql(string nodeName, string database, string sql)
		{
			using (SqlConnection conn = GetConnection(nodeName, database))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					cmd.CommandTimeout = TSQLCommandTimeout.Seconds;
					cmd.ExecuteNonQuery();
				}
			}
		}

		/// <summary>
		/// Runs sql statement against specified node and database, ignoring any exceptions that occur.
		/// </summary>
		static void xsql_ne(string nodeName, string database, string sql)
		{
			try
			{
				xsql(nodeName, database, sql);
			}
			catch (Exception)
			{
				// Ignoring exception on purpose here.
			}
		}

		/// <summary>
		/// Runs sql statement against given server and database.
		/// Allows specifying a number of retries as well as an error tag to display during failure.
		/// Used specifically for statements against AlwaysOn that can fail during failover and 
		/// need to be retried, for example resuming the database after failover.
		/// </summary>
		static void xsql(string nodeName, string sql, string database, int retryAttempts, string errorTag)
		{
			for (int i=1; i<=retryAttempts; i++)
			{
				try
				{
					xsql(nodeName, sql, database);
					return;
				}
				catch (Exception ex)
				{
					LogStatus("{0} => {1}", errorTag, ex.Message);
					Sleep(TSQLRetryInterval);
				}
			}
		}

		/// <summary>
		/// Cleans up exception messages removing embedded carriage returns, line feeds and tabs.
		/// </summary>
		static string CleanExceptionMessage(Exception ex)
		{
			string msgToClean = ex.Message;
			string sqlErrorCode = "";

			// Extract error code from SqlException and place in front of exception.
			if (ex is System.Data.SqlClient.SqlException)
			{
				System.Data.SqlClient.SqlException sqlEx = (System.Data.SqlClient.SqlException) ex;
				sqlErrorCode = "[" + sqlEx.ErrorCode.ToString() + "]";
			}

			// Remove linefeeds and carriage returns and tabs.
			msgToClean = msgToClean.Replace('\n', ' ');
			msgToClean = msgToClean.Replace('\r', ' ');
			msgToClean = msgToClean.Replace('\t', ' ');
			return sqlErrorCode  + msgToClean;
		}
	 }
}